﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.project.dao.impl;

import java.util.*;

import com.hh.bss.auth.util.AuthSqlUtil;
import com.hh.bss.base.*;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.cache.SystemUserCache;
import com.hh.bss.common.page.*;
import com.hh.bss.common.page.impl.*;
import com.hh.bss.project.dao.*;
import com.hh.bss.project.entity.*;
import com.hh.bss.project.service.*;
import com.hh.bss.project.vo.query.*;
import com.hh.bss.util.*;





import static com.hh.bss.util.ObjectUtils.*;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

@Repository
public class AccountPnDaoImpl extends BaseHibernateDao<AccountPn,java.lang.Integer>  implements AccountPnDao<AccountPn,java.lang.Integer>{

	public Class getEntityClass() {
		return AccountPn.class;
	}	
	public Page findPage(AccountPnQuery query) {
        //XsqlBuilder syntax,please see http://code.google.com/p/rapid-xsqlbuilder
        // [column]为字符串拼接, {column}为使用占位符. [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接 
        // [column] 为PageRequest的属性
        
		 Class[] os={ContractJobCvBackgroundQuery.class};
 		Class property= org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		 if (property!=null){
        	 if ("java.lang.String".equals(property.getName())){
	    			
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(),query.getQuery());
	    			
	    		}
	    		else if ("java.lang.Integer".equals(property.getName())){
	    			
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));
	    			
	    		}
	    		else if ("java.lang.Double".equals(property.getName())){
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
	    		}
	    		else if ("java.util.Date".equals(property.getName())){
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
	    		}
        }	 
        //生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
        StringBuffer sql2 = new StringBuffer();
        List values = new ArrayList();
        sql2.append("select * from ( ");
        sql2.append("select a.id,a.offer_id offerId,a.nbr,a.final_ar finalAr,a.template_type templateType,a.currency,a.branch_nbr branchNbr ");
        sql2.append(" ,a.status,a.export_doc exportDoc,a.upload_doc uploadDoc,a.send_dt sendDt,a.sender,a.comment,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt ");
        sql2.append(" ,b1.cvId,b1.jobId ");
        sql2.append(" ,d.cv_name cvName,d.cv_name_en cvNameEn ");
        sql2.append(" ,e.cust_id custId,e.jobName,e.hrContacter,e.arContacter,ifnull(e.custName,h.abbrName) custName ");
        sql2.append(" ,f.name branchCompanyName ");
        sql2.append(" ,g.arSum,i.acId ");
        sql2.append(" from  account_pn a ");
        sql2.append(" left join (select b.id cvOfferId,c.cv_id cvId,c.job_id jobId from contract_job_cv_offer b,contract_job_cv_recom c  where b.recom_id=c.id ) b1 on a.offer_id=b1.cvOfferId ");
        sql2.append(" left join cv d on b1.cvId=d.id  ");
        sql2.append(" left join (select a.id,a.cust_id,a.name jobName,a.hr_contacter hrContacter,a.ar_contacter arContacter,c.abbr_name custName from contract_job a,customer b,company c where a.cust_id=b.id and b.company_id=c.id) e on b1.jobId=e.id  ");
        sql2.append(" left join system_branch_company f on a.branch_nbr=f.branch_nbr  ");
        //收款记录
        sql2.append(" left join (select a.pn_nbr,sum(a.amount) arSum from account_ar a  group by a.pn_nbr ) g on  g.pn_nbr = a.nbr ");
        sql2.append(" left join (select a.id cId,b.abbr_name abbrName from customer a,company b where a.company_id = b.id  ) h on h.cId = a.cust_id  ");
        //
        sql2.append("left join (select a.cust_id acId from advance_payment a group by a.cust_id ) i on i.acId=e.cust_id ");
        sql2.append(" ) t where 1=1 ");
        if(isNotEmpty(query.getCustId())){
        	 sql2.append(" and  t.custId = ? ");
             values.add(query.getCustId());
        }
        if(isNotEmpty(query.getOfferId())) {
            sql2.append(" and  t.offerId = ? ");
            values.add(query.getOfferId());
        }
        if(isNotEmpty(query.getJobId())) {
            sql2.append(" and  t.jobId = ? ");
            values.add(query.getJobId());
        }
        if(isNotEmpty(query.getCvId())) {
            sql2.append(" and  t.cvId = ? ");
            values.add(query.getCvId());
        }
        if(isNotEmpty(query.getCvName())){
        	 sql2.append(" and  (t.cvName like ?  or t.cvNameEn like ? ) ");
        	 values.add("%"+query.getCvName()+"%");
        	 values.add("%"+query.getCvName()+"%");
        }
        if(isNotEmpty(query.getNbr())) {
            sql2.append(" and  t.nbr like ? ");
            values.add("%"+query.getNbr()+"%");
        }
        if(isNotEmpty(query.getBranchNbr())) {
            sql2.append(" and  t.branchNbr = ? ");
            values.add(query.getBranchNbr());
        }
        
        if(isNotEmpty(query.getSendDtBegin())) {
            sql2.append(" and  t.sendDt >= ? ");
            values.add(query.getSendDtBegin());
        }
        if(isNotEmpty(query.getSendDtEnd())) {
            sql2.append(" and  t.sendDt <= ? ");
            values.add(query.getSendDtEnd());
        }
        if(isNotEmpty(query.getCustName())){
        	sql2.append(" and  t.custName like ? ");
            values.add("%"+query.getCustName()+"%");
        }
      //增加权限控制		
		if (query.getOnlineUser()!=null){
			String authsql=AuthSqlUtil.authPnSQL(query.getOnlineUser().getUsername(), "t.creator");
			if (!StringHelper.isEmpty(authsql)) sql2.append(authsql);
			//System.out.println("add authsql:"+authsql);
		}
		
		if(isNotEmpty(query.getSort())) {
        	sql2.append(" order by t.").append(query.getSort());
        	
        	if(isNotEmpty(query.getDir())) {
        		sql2.append(" ").append(query.getDir());            	
        	}
        }else{
        	sql2.append(" order by t.createDt desc ");
        }
		
        return super.getJdbcDao().createPage(sql2.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}
	
	@Override
	public List findAccountPnListByNbr(String nbr) {

		List list = new ArrayList();
		if (StringUtils.isNotEmpty(nbr)) {
			StringBuffer sql2 = new StringBuffer();
			List values = new ArrayList();
			sql2.append("select * from  account_pn a where 1=1 and a.nbr = ?  ");
			values.add(nbr);
		    list = this.getJdbcDao().findByQuery(sql2.toString(), values.toArray(), new MapObjectRowMapper());
		}
		return list;
	}	

}
